package model;

import java.text.*;
import java.util.*;
import java.sql.*;
import java.sql.Date;

import beans.UserBean;

/**
 * User Data Access Object
 * Class used to elaborate all the queries to the database.
 * Whenever a page on the website requires an operation on "users" (that is, UserBean objects) you should add it to this class
 * following the guidelines proposed by the already implemented methods.
 * Note that all methods are static so whenever you need one of them in a servlet you can call UserDAO.method(params).
 * @author Francesco Anderloni
 * @version 6
 *
 */
public class UserDAO {
	static Connection currentCon = null;
	static ResultSet rs = null;  

	/**
	 * This methods validate the login for a user.
	 * @param bean A UserBean object that incapsulates only the username and password obtained in the login form. All the other variables remains null.
	 * @return An UserBean object containing all the user's data and with the isValid field set to TRUE if the login succeeds; An UserBean object containing the rejected username and password and with the isValid field set to FALSE if it fails.
	 */
	public static UserBean login(UserBean bean) {
		//preparing some objects for connection 
		Statement stmt = null;    

		String username = bean.getEmail();    
		String password = bean.getPassword();   

		String searchQuery =
				"select * from users where email='"
						+ username + "'";

		// "System.out.println" prints in the console; Normally used to trace the process
		System.out.println("Your user name is " + username);          
		System.out.println("Your password is " + password);
		System.out.println("Query: "+searchQuery);

		try {
			//connect to DB 
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);	        
			rs.next();

			//if user exists the user's data is extracted and the isValid variable is set to true
			if (BCrypt.checkpw(password, rs.getString("password"))) {
				String pulledemail = rs.getString(1);
				//String pulledpassword = rs.getString(2);
				String pulledpassword = password;
				String pulledname = rs.getString(3);
				boolean pulledgendermale = rs.getBoolean(4);
				Date pulledbirthdate = rs.getDate(5);
				String pulledphotoURL = rs.getString(6);
				int pulledphone = rs.getInt(7);
				boolean pulledsetting1 = rs.getBoolean(8);
				int pulledsetting2 = rs.getInt(9);
				Timestamp pulledlastlogin = rs.getTimestamp(10);
				Timestamp pulledregdate = rs.getTimestamp(11);
				boolean pulledadminflag = rs.getBoolean(12);
				String pulledcity = rs.getString(13);
				String pulledregion = rs.getString(14);
				String pulledcountry = rs.getString(15);
				int pulledpoints = rs.getInt("points");

				java.util.Date date= new java.util.Date();
				//obtain the current date and time
				Timestamp lastlogin = new Timestamp(date.getTime());

				System.out.println("Welcome " + pulledname);
				System.out.println(pulledphotoURL);
				bean.setEmail(pulledemail);
				bean.setPassword(pulledpassword);
				bean.setName(pulledname);
				bean.setGenderMale(pulledgendermale);
				bean.setBirthDate(pulledbirthdate);
				bean.setPhotoURL(pulledphotoURL);
				bean.setPhone(pulledphone);
				bean.setSetting1(pulledsetting1);
				bean.setSetting2(pulledsetting2);
				bean.setLastLogin(lastlogin);
				bean.setRegistration(pulledregdate);
				bean.setAdmin(pulledadminflag);
				bean.setCity(pulledcity);
				bean.setRegion(pulledregion);
				bean.setCountry(pulledcountry);
				bean.setValid(true);
				bean.setPoints(pulledpoints);

				//finally, update lastlogindate in the database
				stmt.executeQuery("update users set lastlogintimestamp = '"+ lastlogin +"'where email='"
						+ username
						+ "' AND password='"
						+ password
						+ "'");
			}
			else {
				System.out.println("Sorry, you are not a registered user! Please sign up first");
				bean.setValid(false);
			}
		} 

		catch (Exception ex) {
			System.out.println("Log In failed: An Exception has occurred! " + ex);
		} 

		//some exception handling
		finally {
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return bean;
	}	

	/**
	 * This method insert into the database the details of a new user, gathered from the signup form.
	 * @param newUser A UserBean object containing the data that is going to be inserted.
	 * @return The same UserBean, validated by calling the previous login method.
	 */
	public static UserBean signup(UserBean newUser) {
		Statement stmt = null;    

		java.util.Date date= new java.util.Date();
		Timestamp lastlogin = new Timestamp(date.getTime());
		
		String hashedPassword = BCrypt.hashpw(newUser.getPassword(), BCrypt.gensalt());

		//The NULL, "30", and FALSE are default values for attributes that are not requested to the user
		String insertQuery = "insert into users values ('" +
				newUser.getEmail() + "', '" +
				hashedPassword + "', '" +
				newUser.getName() + "', '" +
				newUser.getGenderMale() + "', '" +
				newUser.getBirthDate() + "', 'http://placehold.it/290x290&text=Profile+Pic', NULL, TRUE, 30, '" +
				lastlogin + "', '" + lastlogin + "', FALSE, '" +
				newUser.getCity() + "', '" +
				newUser.getRegion() + "', '" +
				newUser.getCountry() + "');";

		System.out.println("Inserting:" + insertQuery);
		try 
		{
			//connect to DB 
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			stmt.executeQuery(insertQuery);	        
			System.out.println("Insert: Success!"); 

		} 

		catch (Exception ex) 
		{
			System.out.println("Log In failed: An Exception has occurred! " + ex);
		} 

		//some exception handling
		finally 
		{
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		//validate the newly created user
		return login(newUser);

	}

	/**
	 * Method used to retrieve the data of the user which we are visiting. Note that not ALL the data is gathered, but
	 * only the things which are publicly accessible
	 * @param bean An UserBean containing the emai (ID) of the user we are visiting.
	 * @return The same UserBean updated with the user's data.
	 */
	public static UserBean visit(UserBean bean) {

		//preparing some objects for connection 
		Statement stmt = null;    

		String username = bean.getEmail();    

		String searchQuery =
				"select * from users where email='"
						+ username
						+ "'";

		// "System.out.println" prints in the console; Normally used to trace the process
		System.out.println("You requested to visit " + username);          
		System.out.println("Query: "+searchQuery);

		try {
			//connect to DB 
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);	        
			boolean more = rs.next();

			// if user does not exist set the isValid variable to false
			if (!more) {
				System.out.println("Sorry, the user you requested do not exist!");
				bean.setValid(false);
			} 

			//if user exists set the isValid variable to true
			else if (more) {
				String pulledemail = rs.getString(1);
				String pulledname = rs.getString(3);
				boolean pulledgendermale = rs.getBoolean(4);
				Date pulledbirthdate = rs.getDate(5);
				String pulledphotoURL = rs.getString(6);
				int pulledphone = rs.getInt(7);
				boolean pulledsetting1 = rs.getBoolean(8);
				int pulledsetting2 = rs.getInt(9);
				Timestamp pulledlastlogin = rs.getTimestamp(10);
				Timestamp pulledregdate = rs.getTimestamp(11);
				boolean pulledadminflag = rs.getBoolean(12);
				String pulledcity = rs.getString(13);
				String pulledregion = rs.getString(14);
				String pulledcountry = rs.getString(15);
				int pulledpoints = rs.getInt("points");

				System.out.println("Visiting: " + pulledname);
				bean.setEmail(pulledemail);
				bean.setName(pulledname);
				bean.setGenderMale(pulledgendermale);
				bean.setBirthDate(pulledbirthdate);
				bean.setPhotoURL(pulledphotoURL);
				bean.setPhone(pulledphone);
				bean.setSetting1(pulledsetting1);
				bean.setSetting2(pulledsetting2);
				bean.setLastLogin(pulledlastlogin);
				bean.setRegistration(pulledregdate);
				bean.setAdmin(pulledadminflag);
				bean.setCity(pulledcity);
				bean.setRegion(pulledregion);
				bean.setCountry(pulledcountry);
				bean.setValid(true);
				bean.setPoints(pulledpoints);
			}
		} 

		catch (Exception ex) {
			System.out.println("Transfer failed: An Exception has occurred! " + ex);
		} 

		//some exception handling
		finally {
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return bean;
	}

	/**
	 * Method used to search a user through the searchuser.jsp page
	 * @param bean A UserBean object containing the search details inserted in the search form.
	 * @return An ArrayList of UserBeans containing the search results.
	 */
	public static ArrayList<UserBean> search(UserBean bean) {

		//preparing some objects for connection 
		Statement stmt = null; 
		String email = bean.getEmail();  
		String name = bean.getName();
		String country = bean.getCountry();
		String city = bean.getCity();

		String searchQuery = "select email,name,city,country,lastlogintimestamp,registrationtimestamp from users where email ilike '%" + email + "%' and name ilike '%" + name + "%' and city ilike '%" + city + "%' and country ilike '%" + country + "%' order by name ASC";

		// "System.out.println" prints in the console; Normally used to trace the process
		System.out.println("You searched for " + email + ", " + name + ", "+ country + ", " + city);          
		System.out.println("Query: "+searchQuery);

		ArrayList<UserBean> searchResults = new ArrayList<UserBean>();

		try {
			//connect to DB 
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);	        

			//if user exists set the isValid variable to true
			while (rs.next()) {

				UserBean user = new UserBean();
				String pulledemail = rs.getString(1);
				String pulledname = rs.getString(2);
				Timestamp pulledlastlogin = rs.getTimestamp(5);
				Timestamp pulledregdate = rs.getTimestamp(6);
				String pulledcity = rs.getString(3);
				String pulledcountry = rs.getString(4);

				System.out.println("Result: " + pulledname);
				user.setEmail(pulledemail);
				user.setName(pulledname);
				user.setLastLogin(pulledlastlogin);
				user.setRegistration(pulledregdate);
				user.setCity(pulledcity);
				user.setCountry(pulledcountry);
				//add the result to the ArrayList
				searchResults.add(user);

			}
		} 

		catch (Exception ex) {
			System.out.println("Transfer failed: An Exception has occurred! " + ex);
		} 

		//some exception handling
		finally {
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return searchResults;
	}

	public static void updatePhotoURL(UserBean user) {
		Statement stmt = null;    

		String updateQuery = "update users set photourl='" + user.getPhotoURL() + "' where email='" + user.getEmail() + "'";

		System.out.println("Updating User's Photo URL:" + updateQuery);
		try 
		{
			//connect to DB 
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			stmt.executeUpdate(updateQuery);	        
			System.out.println("Shiny new profile pic"); 
		} 

		catch (Exception ex) 
		{
			System.out.println("Log In failed: An Exception has occurred! " + ex);
		} 

		//some exception handling
		finally 
		{
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
	}
}
